﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Web;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace GONES.Tools.Common
{
    public class ExcelHelper
    {
        public static string BulidFileName()
        {
            string Fname = "~/Export/" + DateTime.Now.ToString("yyyy-MM-dd").Replace('-', '/') + "/";
            Fname = HttpContext.Current .Server.MapPath(Fname);
            if (!Directory.Exists(Fname))
                Directory.CreateDirectory(Fname);
            return Fname;
        }

        /// <summary>
        /// 无参构造方法
        /// </summary>
        public ExcelHelper()
        {
        }
        public ExcelHelper(string filename)
        {
            FileName = filename;
        }

        private string[] _columnname = new string[0];
        /// <summary>
        /// 列字段
        /// </summary>
        public string[] ColumnName
        {
            get { return _columnname; }
            set { _columnname = value; }
        }

        private DelegateObject[] _Delegate = null;
        public DelegateObject[] Delegate
        {
            set
            {
                _Delegate = value;
            }
            get
            {
                return _Delegate;
            }
        }
        private string[] _columntitle = new string[0];
        /// <summary>
        /// 表头信息
        /// </summary>
        public string[] ColumnTitle
        {
            get { return _columntitle; }
            set { _columntitle = value; }
        }

        /// <summary>
        /// 数据总数
        /// </summary>
        public int Count { get; set; }
        /// <summary>
        /// 文件保存路径
        /// </summary>
        public string FileName { get; set; }
        /// <summary>
        /// 文档标题
        /// </summary>
        public string DocumentTitle { get; set; }
        /// <summary>
        /// 公司
        /// </summary>
        public string Company { get; set; }

        private int _PageSize = 10000;
        /// <summary>
        /// 每页存放行数
        /// </summary>
        public int PageSize
        {
            get { return _PageSize; }
            set { _PageSize = value; }
        }
        /// <summary>
        /// 主题
        /// </summary>
        private string Subject = "PZT Document";
        /// <summary>
        /// 错误信息
        /// </summary>
        public string ErrorMessage { get; set; }

        /// <summary>
        /// 执行所耗毫秒
        /// </summary>
        public double ComTime { get; set; }
        public DbDataReader DataReader { get; set; }
        public DataTable DataTable { get; set; }
        public DataView DataView { get; set; }
        public IList DataIlist { get; set; }

        /// <summary>
        /// Ilist 转换成 DataTable
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        private DataTable ConvertToDataTable(IList list)
        {

            if (list == null || list.Count <= 0)
            {
                return null;
            }

            DataTable dt = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    dt.Columns.Add(pi.Name, pi.PropertyType);
                }

                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        object obj = pi.GetValue(list[i], null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    dt.LoadDataRow(array, true);
                }
            }
            return dt;
        }
        /// <summary>
        /// 转换数据源
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public static DataTable IlistToDataTable(IList list)
        {
            DataTable dt = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    dt.Columns.Add(pi.Name, pi.PropertyType);
                }

                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        object obj = pi.GetValue(list[i], null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    dt.LoadDataRow(array, true);
                }
            }
            return dt;
        }

        /// <summary>
        /// 将数据源转换成IList并记录数据的总量
        /// </summary>
        /// <returns></returns>
        private DataTable ConvertToTable()
        {
            if (DataTable != null)
            {
                Count = DataTable.Rows.Count;
                return DataTable;
            }
            else if (DataView != null)
            {
                Count = DataView.Table.Rows.Count;
                return DataView.Table;
            }
            else if (DataReader != null)
            {
                if (DataReader.IsClosed || DataReader.FieldCount == 0 || !DataReader.HasRows)
                    return null;
                try
                {
                    DataTable objDataTable = new DataTable();
                    int intFieldCount = DataReader.FieldCount;
                    for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
                    {
                        objDataTable.Columns.Add(DataReader.GetName(intCounter), DataReader.GetFieldType(intCounter));
                    }
                    objDataTable.BeginLoadData();
                    object[] objValues = new object[intFieldCount];
                    while (DataReader.Read())
                    {
                        Count++;
                        DataReader.GetValues(objValues);
                        objDataTable.LoadDataRow(objValues, true);
                    }
                    DataReader.Close();
                    objDataTable.EndLoadData();

                    return objDataTable;
                }
                catch
                {
                    ErrorMessage = "转换出错!";
                }
            }
            else if (DataIlist != null)
            {
                return ConvertToDataTable(DataIlist);
            }
            return null;
        }

        /// <summary>
        /// 填充表头信息
        /// </summary>
        /// <param name="hssfSheet"></param>
        private void FileTitles(HSSFWorkbook hssfworkbook, Sheet hssfSheet)
        {
            HSSFRow hssfRow = hssfSheet.CreateRow(1) as HSSFRow;
            hssfRow.Height = 20 * 20;
            for (int i = 0; i < ColumnName.Length; i++)
            {
                string name = "";
                if (i >= ColumnTitle.Length)
                    name = ColumnName[i];
                else name = ColumnTitle[i];
                Cell cell = hssfRow.CreateCell(i);
                cell.SetCellValue(name);
                HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.CENTER;
                cell.CellStyle = style;
            }
        }

        /// <summary>
        ///  创建Excel 自动分页
        /// </summary>
        public void CreateExcel()
        {
            #region 验证属性是否齐全

            if (DataView == null && DataTable == null && DataReader == null && DataIlist == null)
            {
                ErrorMessage = "数据源不能全为空";
                return;
            }
            if (ColumnName.Length == 0)
            {
                ErrorMessage = "没有表头信息";
                return;
            }
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return;
            }
            #endregion
            DataTable dt = ConvertToTable();
            DateTime dt1 = DateTime.Now;
            int ns = 0; //需要建Sheet的个数
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            if (Company != null)
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = Company;
                hssfworkbook.DocumentSummaryInformation = dsi;
            }
            if (Subject != null)
            {
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Subject = Subject;
                hssfworkbook.SummaryInformation = si;
            }
            ns = (int)Math.Ceiling(Convert.ToDouble((double)Count / _PageSize));
            for (int i = 0; i < ns; i++)
            {
                HSSFSheet sheet;
                sheet = (HSSFSheet)hssfworkbook.CreateSheet("infozr_sheet_" + (i+1).ToString());
                //设置标题
                HSSFRow row = (HSSFRow)sheet.CreateRow(0);
                row.Height = 40 * 20;
                HSSFCell cell = (HSSFCell)row.CreateCell(0);
                cell.SetCellValue(DocumentTitle);
                HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.CENTER;
                HSSFFont font = (HSSFFont)hssfworkbook.CreateFont();
                font.FontHeight = 20 * 20;
                style.SetFont(font);
                cell.CellStyle = style;

                FileTitles(hssfworkbook, sheet);
                sheet.AddMergedRegion(new Region(0, 0, 0, ColumnName.Length - 1));
                //冻结前两行
                sheet.CreateFreezePane(0, 2, 0, 2);
                //设置网格线查看时是否可见
                sheet.DisplayGridlines = true;
                //设置网格线打印时是否可见
                sheet.IsPrintGridlines = true;
                int k = i * _PageSize;
                int end = (i + 1) * _PageSize;
                if (end > Count)
                    end = Count;
                for (int q = k; q < end; q++)
                {
                    HSSFRow rowi = (HSSFRow)sheet.CreateRow(q - k + 2);
                    rowi.Height = 16 * 20;
                    for (int j = 0; j < ColumnName.Length; j++)
                    {
                        HSSFCell celljj = (HSSFCell)rowi.CreateCell(j);
                        string v = DataString(j, dt.Rows[q][ColumnName[j]].ToString());
                        if (!string.IsNullOrEmpty(ErrorMessage))
                            return;
                        celljj.SetCellValue(v);
                    }
                }
            }
            //保存
            if (!string.IsNullOrEmpty(FileName))
            {
                FileStream file = new FileStream(FileName, FileMode.Create);
                hssfworkbook.Write(file);
                file.Close();
                file.Dispose();
            }
            //else
            //    hssfworkbook.Write(OutStream);
            DateTime dt2 = DateTime.Now;
            TimeSpan ts = dt2 - dt1;
            ComTime = ts.TotalMilliseconds;
        }

        /// <summary>
        /// 追加数据
        /// </summary>
        public void AppendExcel()
        {
            #region 验证属性是否齐全
            if (DataView == null && DataTable == null && DataReader == null && DataIlist == null)
            {
                ErrorMessage = "数据源不能全为空";
                return;
            }
            if (ColumnName.Length == 0)
            {
                ErrorMessage = "没有表头信息";
                return;
            }
            if (string.IsNullOrEmpty(FileName))
            {
                ErrorMessage = "文件名不能为空";
                return;
            }
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return;
            }
            #endregion
            DataTable dt = ConvertToTable();
            DateTime dt1 = DateTime.Now;
            int sheets = 0; //流中的Sheet个数
            int lastsheetrows = 0; //流中的最后一个Sheet中的已有数据行数
            int lrows = 0;
            int ns = 0; //目前还需要建多少个Sheet
            HSSFWorkbook hssfworkbook;
            if (File.Exists(FileName))
            {
                using (FileStream file = new FileStream(FileName, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
                sheets = hssfworkbook.NumberOfSheets - 1;
                lastsheetrows = hssfworkbook.GetSheetAt(sheets).LastRowNum + 1;
            }
            else
            {
                hssfworkbook = new HSSFWorkbook();
                if (Company != null)
                {
                    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                    dsi.Company = Company;
                    hssfworkbook.DocumentSummaryInformation = dsi;
                }
                if (Subject != null)
                {
                    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                    si.Subject = Subject;
                    hssfworkbook.SummaryInformation = si;
                }
            }
            if (lastsheetrows >= _PageSize + 2)
            {
                lastsheetrows = 0;
                sheets++;
            }
            lrows = lastsheetrows-2;
            if (lrows < 0)
                lrows = 0;
            ns = (int)Math.Ceiling(Convert.ToDouble((Count + lrows) / (double)_PageSize));
            for (int i = sheets; i < sheets + ns; i++)
            {
                HSSFSheet sheet;
                if (lastsheetrows == 0)
                {
                    sheet = (HSSFSheet)hssfworkbook.CreateSheet("Sheet" + (i + 1).ToString());
                    //设置标题
                    HSSFRow row = (HSSFRow)sheet.CreateRow(0);
                    row.Height = 40 * 20;
                    HSSFCell cell = (HSSFCell)row.CreateCell(0);
                    cell.SetCellValue(DocumentTitle);
                    HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
                    style.Alignment = HorizontalAlignment.CENTER;
                    HSSFFont font = (HSSFFont)hssfworkbook.CreateFont();
                    font.FontHeight = 20 * 20;
                    style.SetFont(font);
                    cell.CellStyle = style;
                    FileTitles(hssfworkbook, sheet);
                    sheet.AddMergedRegion(new Region(0, 0, 0, ColumnName.Length - 1));
                    //冻结前两行
                    sheet.CreateFreezePane(0, 2, 0, 2);
                    //设置网格线查看时是否可见
                    sheet.DisplayGridlines = true;
                    //设置网格线打印时是否可见
                    sheet.IsPrintGridlines = true;
                    lastsheetrows = 2;
                }
                else
                {
                    sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheets);
                }
                int k = (i - sheets) * _PageSize;
                if (k > 0)
                    k = k - lrows ;
                int end = k + _PageSize - lastsheetrows + 2;
                if (end > Count)
                    end = Count;
                for (int q = k; q < end; q++)
                {
                    HSSFRow rowi = (HSSFRow)sheet.CreateRow(q - k + lastsheetrows);
                    rowi.Height = 16 * 20;
                    for (int j = 0; j < ColumnName.Length; j++)
                    {
                        HSSFCell celljj = (HSSFCell)rowi.CreateCell(j);
                        string v = DataString(j, dt.Rows[q][ColumnName[j]].ToString());
                        if (!string.IsNullOrEmpty(ErrorMessage))
                            return;
                        celljj.SetCellValue(v);
                    }
                }
                lastsheetrows = 0;
            }
            //保存
            if (!string.IsNullOrEmpty(FileName))
            {
                FileStream file = new FileStream(FileName, FileMode.Create);
                hssfworkbook.Write(file);
                file.Close();
                file.Dispose();
            }
            //else
            //    hssfworkbook.Write(OutStream);
            DateTime dt2 = DateTime.Now;
            TimeSpan ts = dt2 - dt1;
            ComTime = ts.TotalMilliseconds;
        }

        private string DataString(int fieldIndex, string str)
        {
            if (this._Delegate == null || this._Delegate.Length <= fieldIndex || this._Delegate[fieldIndex] == null)
                return str;
            try
            {
                return Delegate[fieldIndex](str).ToString();
            }
            catch (Exception xDelegate)
            {
                ErrorMessage = xDelegate.Message;
                return "";
            }
        }
        /// <summary>
        /// 读取Excel文件获取数据返回DataTable
        /// </summary>
        /// <returns></returns>
        public DataSet GetDataSet()
        {
            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(FileName, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            DataSet dataSet = new DataSet();
            for (int n = 0; n < hssfworkbook.NumberOfSheets; n++)
            {
                HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(n);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                int cols = ColumnName.Length;
                DataTable dt = new DataTable();
                for (int j = 0; j < cols; j++)
                {
                    dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
                }
                int k = 0;
                try
                {
                    
                    while (rows.MoveNext())
                    {

                        HSSFRow row = (HSSFRow) rows.Current;
                        DataRow dr = dt.NewRow();
                        for (int i = 0; i < row.LastCellNum; i++)
                        {
                            HSSFCell cell = (HSSFCell) row.GetCell(i);
                            if (cell == null)
                            {
                                dr[i] = null;
                            }
                            else
                            {
                                //针对部分数值型取数据时容易出现_和空格问题进行处理
                                dr[i] = cell.ToString().TrimEnd("_ ".ToCharArray());
                            }
                        }
                        dt.Rows.Add(dr);
                        k++;
                    }
                }
                catch (Exception )
                    {
                        
                        throw new ExceptionZj(string.Format("第{0}数据有问题",k ));
                    }
                  
                   
                
                dataSet.Tables.Add(dt);
            }
            return dataSet;
        }
    }
    
    public delegate bool DelegateBool(object data);

    public delegate string DelegateString(object data);

    public delegate object DelegateObject(object data);
}

public class ExceptionZj:Exception
{
          private string mymessage;
          public ExceptionZj(string message) 
                                : base() 
        { 
                mymessage = message; 
        } 
                
        public override string Message 
        { 
            get 
            { 
                    return mymessage; 
            } 
        }
}
